/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pt.isel.medknow.tables.api;

import java.io.Serializable;
import java.util.Date;
import java.util.List;
import org.hibernate.Session;
import pt.isel.beans.SelectBean;
import pt.isel.medknow.HibernateUtil;
import pt.isel.medknow.tables.*;

/**
 *
 * @author fallen
 */
public class HibMethods {

    public static void testMethods() {

        HibMethods m = new HibMethods();

        //Adding doctor
        // dm.addDoctor("DOCTOR1", 1);
//        fm.addFilial("Filial_2");
//        fm.addFilial("Filial_3");
        // m.addPatient("HUI", new Date());

//        Visit visit = m.addVisit(1, 1, new Date());
//        System.out.println("visit = " + visit);
//        
//        m.addVisitInfo(visit.getVisit(), "yes", "dist", "myope", "hard");
//        System.out.println("visit = " + visit);


//        // Listing added books
//        List<Doctor> doctors = m.listDoctorsBetween("2012-12-31", "2013-11-01");
//        for (Doctor doctor : doctors) {
//            System.out.println(doctor.toString());
//        }
    }
    //Using persistent objects to store data into the database.
    
    //<editor-fold defaultstate="collapsed" desc="utils">
    public Serializable saveObject(Object object) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        Serializable saved = session.save(object);
        session.getTransaction().commit();
        return saved;
    }
    
    public void deleteObject(Class cl, Serializable id) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        session.delete(session.load(cl, id));
        session.getTransaction().commit();
    }
    
    public List selectClass(Class cl, String query) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        List list = session.createSQLQuery(query).addEntity(cl).list();
        session.getTransaction().commit();
        return list;
    }
    
    public int updateSQL(String query) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        int res = session.createSQLQuery(query).executeUpdate();
        session.getTransaction().commit();
        return res;
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="doctors">
    public void addDoctor(String doctorName, Integer filialId) {
        Doctor doctor = new Doctor();
        doctor.setName(doctorName);
        doctor.setFilialId(filialId);
        saveObject(doctor);
    }
    
    // Native SQL Query - FIXME, should not use native sql, could be sql injection
    // date1 must be < date2
    public List<Doctor> listDoctorsBetween(String date1, String date2) {
        String q = String.format("SELECT * FROM doctors "
                + "LEFT JOIN visits on doctors.doctor_id = visits.doctor_id "
                + "WHERE date >= '%s' AND date <= '%s'", date1, date2);
        return selectClass(Doctor.class, q);
    }

    public void deleteDoctor(Integer doctorId) {
        updateSQL(String.format("DELETE FROM visits WHERE doctor_id = %s;", doctorId));
        updateSQL(String.format("DELETE FROM doctors WHERE doctor_id = %s;", doctorId));
    }

    //Native SQL Query
    public List<Doctor> listDoctors() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        List list = session.createSQLQuery("select * from doctors").addEntity(Doctor.class).list();
        session.getTransaction().commit();
        return list;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="patients">
    public List<Patient> listPatients() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        List<Patient> list = session.createSQLQuery("select * from patients").addEntity(Patient.class).list();
        session.getTransaction().commit();
        return list;
    }
    
    public void deletePatient(Integer patientId) {
        updateSQL(String.format("DELETE FROM visits WHERE patient_id = %s;", patientId));
        updateSQL(String.format("DELETE FROM patients WHERE patient_id = %s;", patientId));
    }

    public void addPatient(String patientName, Date dateOfBirth) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        Patient patient = new Patient();
        patient.setName(patientName);
        patient.setDateOfBirth(dateOfBirth);
        session.save(patient);
        session.getTransaction().commit();
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="visits">
    public List<Visit> listVisit() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        List<Visit> list = session.createSQLQuery("select * from visits").addEntity(Visit.class).list();
        session.getTransaction().commit();
        return list;
    }
    
    public Visit addVisit(Integer patientId, Integer doctorId, Date date) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        Visit visit = new Visit();
        visit.setPatientId(patientId);
        visit.setDoctorId(doctorId);
        visit.setDate(date);
        session.save(visit);
        session.getTransaction().commit();
        return visit;
    }
    
    public void deleteVisit(Integer patientId, Integer doctorId, Date date) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        String q = String.format("DELETE from visits where patient_id='%s' "
                + "AND doctor_id='%s' AND date='%s'", patientId, doctorId, 
                SelectBean.dateFormat.format(date));
        session.createSQLQuery(q).executeUpdate();
        session.getTransaction().commit();
    }

    public Visit getVisit(Integer visitId) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        Visit visit;
        try {
            String q = String.format("SELECT * FROM visits "
                    + "WHERE visit_id=%s", visitId.toString());
            List<Visit> list = session.createSQLQuery(q).addEntity(Visit.class).list();
            visit = list.iterator().next();
        } catch (Exception ex) {
            visit = null;
        }
        session.save(visit);
        session.getTransaction().commit();
        return visit;
    }
    
    public Visit getVisit(Integer doctorId, Integer patientId, Date date) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        Visit visit;
        try {
            String q = String.format("SELECT * FROM visits "
                    + "WHERE patient_id='%s' AND doctor_id='%s' AND "
                    + "date='%s'", patientId.toString(), doctorId.toString(), 
                    SelectBean.dateFormat.format(date));
            List<Visit> list = session.createSQLQuery(q).addEntity(Visit.class).list();
            visit = list.iterator().next();
        } catch (Exception ex) {
            visit = null;
        }
        session.save(visit);
        session.getTransaction().commit();
        return visit;
    }

    public void addVisitInfo(Integer visitId,
            String astigmatic, String tearRate, String prescription, String lenses) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        Visit visit = (Visit) session.load(Visit.class, visitId);
        visit.setAstigmatic(astigmatic);
        visit.setTearRate(tearRate);
        visit.setPrescription(prescription);
        visit.setLenses(lenses);
        session.flush();
        session.getTransaction().commit();
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="filials">
    public List<Filial> listFilials() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        List list = session.createSQLQuery("select * from filials").addEntity(Filial.class).list();
        session.getTransaction().commit();
        return list;
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="Roles">
    public List<RefUserRole> listRoles() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        List<RefUserRole> list = session.createSQLQuery("select * from ref_user_role").addEntity(RefUserRole.class).list();
        session.getTransaction().commit();
        return list;
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="Users">
    public void addUser(String userLogin, String userPassword, String selectedRole) {
        // dont touch him!!!
        if (userLogin.equalsIgnoreCase("tomcat")) {
            return;
        }
        deleteUser(userLogin);
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        String q = String.format("INSERT INTO ref_user_account VALUES('%s', MD5('%s'))",
                userLogin, userPassword);
        session.createSQLQuery(q).executeUpdate();
        q = String.format("INSERT INTO ref_user_account_role VALUES('%s', '%s')",
                userLogin, selectedRole);
        session.createSQLQuery(q).executeUpdate();
        session.getTransaction().commit();
    }

    public List<RefUserAccount> listUsers() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        List<RefUserAccount> list = session.createSQLQuery(
                "select * from ref_user_account "
                + "WHERE username != 'tomcat'").
                addEntity(RefUserAccount.class).list();
        session.getTransaction().commit();
        return list;
    }

    public void deleteUser(String selectedUser) {
        // dont touch him!!!
        if (selectedUser.equalsIgnoreCase("tomcat")) {
            return;
        }
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        String q = String.format("DELETE FROM ref_user_account_role "
                + "WHERE username='%s'", selectedUser);
        session.createSQLQuery(q).executeUpdate();
        q = String.format("DELETE FROM ref_user_account WHERE username='%s'",
                selectedUser);
        session.createSQLQuery(q).executeUpdate();
        session.getTransaction().commit();
    }
    //</editor-fold>
}
